package com.imooc.framework.excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.Date;


/**
 * @author zeb
 * @date 2021-09-30
 **/
public class MyExcelInput {
    public static void main(String[] args) throws Exception {
        String fileName = "D:\\TEST4.xlsx";
        readExcel(fileName);
    }

    public static void readExcel(String fileName) throws Exception {
//        读取文件
        InputStream inputStream = Files.newInputStream(Paths.get(fileName));
//        找到工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);//xls接收对象
//       找到对应的sheet
        Sheet sheet = workbook.getSheetAt(0);//0表示第一个sheet
//        得到物理行数
        int rowCount = sheet.getPhysicalNumberOfRows();
//        根据物理行数，读取每一格内容
        for (int i = 0; i < rowCount; i++) {
            System.out.println("行数" + rowCount);
//            读取每一行内容
            Row rowData = sheet.getRow(i);
            if (rowData != null) {
//                由物理列数，得到总共由几列
                int cellCount = rowData.getPhysicalNumberOfCells();

                System.out.println("列数" + cellCount);

                System.out.println("第" + i + "行，长度：" + cellCount );

                for (int j = 0; j < cellCount; j++) {
                    //读取每一行每一列的值
                    Cell cell = rowData.getCell(j);
                    String cellValue = "";
                    if (cell != null) {
//                        获取类型，根据类型获取对应的值
                        CellType cellType = CellType.forInt(cell.getCellType());
                        switch (cellType) {
//                            case HSSFCell.CELL_TYPE_BOOLEAN: //poi3.15开始已不能用
                            case STRING: //CellType.STRING
                                System.out.println("字符串类型");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.println("布尔类型");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case _NONE:
                                System.out.println("无数据null");
                                break;
                            case NUMERIC:
//                                short format = cell.getCellStyle().getDataFormat();
////                                数值类型有两种 时间戳和数值
//                                if (DateUtil.isCellDateFormatted(cell)) {
//                                    System.out.println("日期");
//                                    Date date = cell.getDateCellValue();
//                                    cellValue = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
//                                } else {
//                                    System.out.println("数值类型");
//                                    cell.setCellType(CellType.STRING);
//                                    cellValue = cell.toString();
//                                }
                                cellValue = getMonthDay(cell);
                                break;
                            case ERROR:
                                System.out.println("数据类型错误");
                                break;
                            case BLANK:
                                System.out.println("无数据");
                                break;
                            case FORMULA:
                                System.out.println("公式类型数据");
//                                得到单元格使用的公式
                                String cellFormula = cell.getCellFormula();
                                System.out.println("使用的公式是：" + cellFormula);
//                                得到计算数据
                                double numericCellValue = cell.getNumericCellValue();
                                System.out.println("计算数据为：" + numericCellValue);
                                break;
                        }
                    }
                    System.out.println(cellValue);
                }
                System.out.println("第" + (i + 1) + "行" + "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^");
            }
        }


        inputStream.close();
    }

    public static String getMonthDay(Cell cell){
        short format = cell.getCellStyle().getDataFormat();
        SimpleDateFormat sdf = null;
        if(format == 58){
            sdf = new SimpleDateFormat("M月dd日");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            return sdf.format(date);
        }
        return "";
    }
}
